<?php
declare (strict_types=1);

namespace app\api\model\boss;

use app\common\model\goods\Service as ServiceModel;
use app\common\enum\order\OrderStatus as OrderStatusEnum;
use app\common\enum\order\PayStatus as OrderPayStatusEnum;
use app\api\model\{OrderSub as OrderSubModel,
    OrderRefund as OrderRefundModel,
    Order as OrderModel,
    OrderGoods as OrderGoodsModel
};
use think\facade\Db;

class MRetailOrderV2 extends ServiceModel
{
    // 标价应收
    public function income($selectParams)
    {
        $payTypeSql = ' 1 = 1 ';
        $total_pay_price = ' g.total_pay_price ';
        $total_price = ' g.total_price ';
        switch ($selectParams['payType']) {
            case 1: // 现金支付方式
                $payTypeSql = ' realPayType = 20 ';
                break;
            case 2: // 三券支付方式
                $payTypeSql = ' realPayType in (7,6,5) ';
                $total_pay_price = ' g.deduction_value ';
                $total_price = ' g.deduction_value as total_price ';
                break;
            case 4: // 充值支付方式
                $payTypeSql = ' realPayType = 10 ';
                break;
        }
        $sql = 'select sum(total) as total,sum(pay_price) as discount_price,sum(realIncome) as realIncome, sum(cost_money) as cost_money,count(*) as orderNum,sum(goodsNum) as goodsNum from (
        select *,(sum(total_price)- sum(refund_total_price)) as total,sum(total_pay_price) as pay_price,sum(total_num) as goodsNum,sum(cost_price) as cost_money,(pay_money+bd_income) as realIncome  from (
        select  r.refund_money,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_num) as total_num,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.cost_price) as cost_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,r.refund_money,0) as refund_total_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,g.total_pay_price-r.refund_money,'.$total_pay_price.') as total_pay_price,o.order_id,o.order_parent_id,'. $total_price .',s.pay_money,case s.bd_type when 7 then round(s.deduction_value * 0.5,2) when 6 then 0 when 5 then 0 when 10 then round(s.deduction_value,2) else s.deduction_value end  as bd_income,if(s.bd_type =  0,s.pay_type,s.bd_type) as realPayType
        from yoshop_order_goods g
        left join yoshop_order o on o.order_id = g.order_id
        left join yoshop_order_sub s on s.order_id = g.order_parent_id
        left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id
        where o.create_time >= ' . $selectParams['startTime'] . ' and o.create_time <= ' . $selectParams['endTime'] . ' and o.pay_status = ' . OrderPayStatusEnum::SUCCESS . ' and (o.order_status = ' . OrderStatusEnum::COMPLETED . ' or o.order_status = ' . OrderStatusEnum::NORMAL . ')
        ) a group by order_parent_id,realPayType ) b where ' . $payTypeSql;
        $result = Db::query($sql);
        return $result;

    }

    // 订单收入明细按照订单维度
    public function orderPayDetail($selectParams)
    {
        $payTypeSql = ' 1 = 1 ';
        switch ($selectParams['payType']) {
            case 1: // 现金支付方式
                $payTypeSql = ' realPayType = 20 ';
                break;
            case 2: // 三券支付方式
                $payTypeSql = ' realPayType in (7,6,5) ';
                break;
            case 4: // 充值支付方式
                $payTypeSql = ' realPayType = 10 ';
                break;

        }

        $sql = 'select order_id,order_parent_id,cost_money,goodsNum,total,discount_price,realIncome,realPayType as payType  from (	
            select *,(sum(total_price)- sum(refund_total_price)) as total,sum(total_pay_price) as discount_price,sum(total_num) as goodsNum,sum(cost_price) as cost_money,(pay_money+bd_income) as realIncome  from (
            select r.refund_money,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_num) as total_num,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.cost_price) as cost_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,r.refund_money,0) as refund_total_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,g.total_pay_price-r.refund_money,g.deduction_value) as total_pay_price,o.order_id,o.order_parent_id,g.deduction_value as total_price,s.pay_money,case s.bd_type when 7 then round(s.deduction_value * 0.5,2) when 6 then 0 when 5 then 0 when 10 then round(s.deduction_value,2) else s.deduction_value end  as bd_income,if(s.bd_type =  0,s.pay_type,s.bd_type) as realPayType
            from yoshop_order_goods g
            left join yoshop_order o on o.order_id = g.order_id
            left join yoshop_order_sub s on s.order_id = g.order_parent_id
            left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id
            where o.create_time >= ' . $selectParams['startTime'] . ' and o.create_time <= ' . $selectParams['endTime'] . ' and o.pay_status = ' . OrderPayStatusEnum::SUCCESS . ' and (o.order_status = ' . OrderStatusEnum::COMPLETED . ' or o.order_status = ' . OrderStatusEnum::NORMAL . ')
              ) a  GROUP BY order_parent_id,realPayType ) b where ' . $payTypeSql . ' GROUP BY order_parent_id,realPayType';
        $result = Db::query($sql);
        return $result;

    }

    /**
     *  //三券赠送
     * @param $selectParams
     * @param string $type give_bd/give_coupon
     * @return int[]
     */
    public function bangdouData($selectParams, $type = 'give_bd')
    {
        $returnData = [
            'sendBangdou'       => 0,
            'sendBangdouCancel' => 0,
            'usedBangdou'       => 0,
            'usedBangdouCancel' => 0,
        ];

        // 按照赠送查询帮豆数据
        $sql    = 'select * from ( select sum(' . $type . ') as total from yoshop_order_sub where bd_type = 0 and state = 30 and created_time >= "' . $selectParams['startTime'] . '" and created_time <= "' . $selectParams['endTime'] . '")tmp ';
        $result = Db::query($sql);
        if (!empty($result)) {
            $returnData['sendBangdou'] = $result[0]['total'];
        }
        return $returnData;
    }

    /**
     *  新用户注册赠送现金券
     * @param $selectParams
     * @return int[]
     */
    public function regActivity($selectParams)
    {
        $returnData = [
            'sendBangdou'       => 0,
            'sendBangdouCancel' => 0,
            'usedBangdou'       => 0,
            'usedBangdouCancel' => 0,
        ];

        // 按照赠送查询帮豆数据
        $sql    = 'select * from ( select sum(activity_value) as total from yoshop_activity_log where activity_id = 1 and activity_type = 6 and create_time >= "' . $selectParams['startTime'] . '" and create_time <= "' . $selectParams['endTime'] . '")tmp ';
        $result = Db::query($sql);
        if (!empty($result)) {
            $returnData['sendBangdou'] = $result[0]['total'];
        }
        return $returnData;
    }

    // 订单支付明细按照订单，支付方式维度
    public function orderPayMethonDetail($selectParams)
    {
        $sql = 'select order_id,order_parent_id,cost_money,goodsNum,total,discount_price,realIncome,realPayType as payType,deduction_value,cash  from (	
            select *,(sum(total_price)- sum(refund_total_price)) as total,sum(total_pay_price) as discount_price,sum(total_num) as goodsNum,sum(cost_price) as cost_money,bd_income as realIncome from (
            select r.refund_money,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_num) as total_num,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.cost_price) as cost_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,r.refund_money,0) as refund_total_price,if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,g.total_pay_price-r.refund_money,g.deduction_value) as total_pay_price,o.order_id,o.order_parent_id,g.deduction_value as total_price,s.pay_money,case s.bd_type when 7 then round(s.deduction_value * 0.5,2) when 6 then 0 when 5 then 0 when 10 then round(s.deduction_value,2) else s.deduction_value end  as bd_income,if(s.bd_type =  0,s.pay_type,s.bd_type) as realPayType,s.deduction_value as deduction_value,s.pay_money as cash
            from yoshop_order_goods g
            left join yoshop_order o on o.order_id = g.order_id
            left join yoshop_order_sub s on s.order_id = g.order_parent_id
            left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id
            where o.create_time >= ' . $selectParams['startTime'] . ' and o.create_time <= ' . $selectParams['endTime'] . ' and o.pay_status = ' . OrderPayStatusEnum::SUCCESS . ' and (o.order_status = ' . OrderStatusEnum::COMPLETED . ' or o.order_status = ' . OrderStatusEnum::NORMAL . ')
              ) a  GROUP BY order_parent_id,realPayType ) b GROUP BY order_parent_id,realPayType';
        $result = Db::query($sql);
        return $result;
    }
    //营收数据分析，与邦豆生活月订单总支出,暂时作废
    public function orderRealityPayMethon($selectParams)
    {
        $sql = 'select sum(money) as cash,sum(deduction_value) as discount_price,sum(case bd_type when 7 then round(deduction_value * 0.5,2) when 6 then 0 when 5 then 0 when 10 then round(deduction_value,2) else deduction_value end) as realIncome ,bd_type from yoshop_user_balance_log
            where (scene = 10 or scene = 20) and create_time >= ' . $selectParams['startTime'] . ' and create_time <= ' . $selectParams['endTime'] . '  GROUP BY bd_type';
        $result = Db::query($sql);
        $result = array_column($result,null,'bd_type');
        return $result;
    }

    // 商品排名
    public function orderGoodsDetail($selectParams)
    {
        $payTypeSql = ' 1 = 1 ';
        switch ($selectParams['payType']) {
            case 1: // 现金支付方式
                $payTypeSql = ' payType not in (7,6,5) ';
                break;
            case 2: // 三券支付方式
                $payTypeSql = ' payType in (7,6,5) ';
                break;
        }

        $groupSql = '';
        switch ($selectParams['topContent']) {
            case 1:
                $groupSql = ' group by goods_id';
                break;
            case 4:
                $groupSql = ' group by category_id';
                break;
            case 2:
                $groupSql = ' group by parent_id';
                break;
        }

        $orderBySql      = '';
        $staticasGroupby = '';
        switch ($selectParams['rankType']) {
            case 1:
                $orderBySql      = ' where realIncome>0 order by xnum desc,goods_id desc';
                $staticasGroupby = ' GROUP BY goods_id,order_parent_id ';
                break;
            case 2:
                $orderBySql      = ' where realIncome>0 order by bjPrice desc,goods_id desc';
                $staticasGroupby = ' GROUP BY goods_id,order_parent_id ';
                break;
        }

        $returnData = [
            'total' => 0,
            'data'  => [],
        ];

        /*$sql      = 'select *, round((pay_money+bd_income) * ( yjprice / order_price ),2) as realIncome from (
                select tmp.order_price,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_num) as xnum,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.cost_price) as cost_money,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_price) as bjPrice,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_pay_price) as yjPrice,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,s.pay_money) as pay_money,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,case s.bd_type when 7 then round(s.deduction_value * 0.5,2) when 6 then 0 when 5 then 0 else s.deduction_value end) as bd_income,
                o.order_id,o.order_parent_id,g.goods_id,g.goods_name as goods_title,rel.category_id,if(c.parent_id=0, c.category_id,c.parent_id) as parent_id,
                if(s.bd_type = 0,s.pay_type,s.bd_type) as realPayType
                from yoshop_order_goods g
                left join yoshop_order o on o.order_id = g.order_id
                left join yoshop_order_sub s on s.order_id = g.order_parent_id
                left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id
                left join (select goods_id,MAX(category_id) as category_id from yoshop_goods_category_rel GROUP BY goods_id) rel on rel.goods_id = g.goods_id
                left join yoshop_category c on c.category_id = rel.category_id
                left join (select g.order_parent_id,sum(if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_pay_price)) as order_price from yoshop_order_goods g left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id left join yoshop_order o on o.order_id = g.order_id where o.order_status=30 or o.order_status=10 GROUP BY order_parent_id) tmp on tmp.order_parent_id = g.order_parent_id
                where o.create_time >= ' . $selectParams['startTime'] . ' and o.create_time <= ' . $selectParams['endTime'] . ' and o.pay_status = ' . OrderPayStatusEnum::SUCCESS . ' and (o.order_status = ' . OrderStatusEnum::COMPLETED . ' or o.order_status = ' . OrderStatusEnum::NORMAL . ')  order by rel.category_id desc
            ) a ' . $staticasGroupby;*/
        $sql      = 'select *, sum(num) as xnum,sum(cost_price) as cost_money,sum(total_price) as bjPrice,sum(total_pay_price) as yjPrice,sum(pay_money+bd_income) as realIncome from (
                select
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_num) as num,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.cost_price) as cost_price,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.total_price) as total_price,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,g.total_pay_price-r.refund_money,g.total_pay_price) as total_pay_price,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,g.pay_cash) as pay_money,
                if((r.type = 10 or r.type = 30) and r.audit_status=10 and r.status=20,0,case g.bd_type when 7 then round(g.deduction_value * 0.5,2) when 6 then 0 when 5 then 0 else g.deduction_value end) as bd_income,
                o.order_id,o.order_parent_id,g.goods_id,g.goods_name as goods_title,rel.category_id,if(c.parent_id=0, c.category_id,c.parent_id) as parent_id,
                if(g.bd_type = 0,20,g.bd_type) as realPayType
                from yoshop_order_goods g
                left join yoshop_order o on o.order_id = g.order_id
                left join yoshop_order_refund r on r.order_id = g.order_id and r.order_goods_id = g.order_goods_id
                left join (select goods_id,MAX(category_id) as category_id from yoshop_goods_category_rel GROUP BY goods_id) rel on rel.goods_id = g.goods_id
                left join yoshop_category c on c.category_id = rel.category_id
                where o.create_time >= ' . $selectParams['startTime'] . ' and o.create_time <= ' . $selectParams['endTime'] . ' and o.pay_status = ' . OrderPayStatusEnum::SUCCESS . ' and (o.order_status = ' . OrderStatusEnum::COMPLETED . ' or o.order_status = ' . OrderStatusEnum::NORMAL . ')  order by rel.category_id desc
            ) a ' . $staticasGroupby;
        $totalSql = '
        select count(*) as total from (
        select goods_id,sum(xnum) as xnum,goods_title,round(sum(cost_money),2) as cost_money,round(sum(bjPrice),2) as bjPrice,round(sum(yjPrice),2) as yjPrice,round(sum(realIncome),2) as realIncome,parent_id,category_id from ( ' . $sql . ' ) b ' . $groupSql . '  )c ' . $orderBySql;
        $result   = Db::query($totalSql);

        if (empty($result[0]['total'])) {
            return $returnData;
        }
        $returnData['total'] = $result[0]['total'];

        $contentSql = 'select * from (
        select goods_id,sum(xnum) as xnum,goods_title,round(sum(cost_money),2) as cost_money,round(sum(bjPrice),2) as bjPrice,round(sum(yjPrice),2) as yjPrice,round(sum(realIncome),2) as realIncome,parent_id,category_id from ( ' . $sql . ' ) b ' . $groupSql . '  )c ' . $orderBySql . ' limit ' . $selectParams['offset'] . ',' . $selectParams['limit'];

        $rankData = Db::query($contentSql);

        // 提取所有小类和父类
        $allCategoryId       = array_column($rankData, 'category_id');
        $allParentCategoryId = array_column($rankData, 'parent_id');
        $allCategoryIds      = array_merge($allCategoryId, $allParentCategoryId);

        if(empty($allCategoryIds)){
            return $returnData;
        }

        // 大类查询
        $allCategoryIds     = implode(',', $allCategoryIds);
        $categorySql        = 'select category_id,name from yoshop_category where category_id in (' . $allCategoryIds . ')';
        $category           = Db::query($categorySql);
        $parentCategoryData = [];
        foreach ($category as $key => $value) {
            $parentCategoryData[$value['category_id']] = $value['name'];
        }

        foreach ($rankData as $key => $value) {
            $rankData[$key]['categoryName']    = $parentCategoryData[$value['parent_id']];
            $rankData[$key]['sonCategoryName'] = $parentCategoryData[$value['category_id']];
        }

        $returnData['data'] = $rankData;
        return $returnData;
    }


    // 备注方法
    public function note()
    {
        // 组合支付方式计算成本方式 = 成本 * （支付方式金额 / 订单总金额 ）
        // 支付方式对应标价营收 = 订单标价营收 * （业绩营收对应支付金额 / 业绩营收订单总金额）
    }

    // 查询邦豆赠送记录
    public function bangdouRewardMoney($selectParams)
    {
        $sql = 'select ifnull(sum(sendBangdou),0) as total from (';
        for ($i = date('Y', $selectParams['startTime']); $i <= date('Y', $selectParams['endTime']); $i++) {
            $sql .= 'select sum(give_bd) as sendBangdou from yoshop_order_sub where bd_type = 0 and state = 30 and created_time >= ' . $selectParams['startTime'] . ' and created_time <=' . $selectParams['endTime'];
            if ($i != date('Y')) {
                $sql .= ' union all ';
            }
        }
        $sql    .= ' ) a ';
        $result = Db::query($sql);
        return $result[0]['total'];

    }

    /**
     *  商业充值
     * @param $selectParams
     * @return int[]
     */
    public function commercialRecharge($selectParams)
    {
        $returnData = [
            'rechargeAmount'       => 0,
            'rechargeAmountCancel' => 0,
        ];

        // 按照赠送查询帮豆数据
        $sql    = 'select * from ( select sum(deduction_value) as total from yoshop_user_balance_log where bd_type = 10 and scene = 40 and create_time >= "' . $selectParams['startTime'] . '" and create_time <= "' . $selectParams['endTime'] . '")tmp ';
        $result = Db::query($sql);
        if (!empty($result)) {
            $returnData['rechargeAmount'] = $result[0]['total'];
        }
        return $returnData;
    }
}
